#!/bin/bash
##################################################################
## function:hiveToMysql 折扣三期流量相关数据
## eg：bash track_z_kpi.sh 20150505
## author: shx
## createdate: 20150505 
## version:     
##################################################################
#import 
. /home/hadoop/shx/baseUtil.sh
echo ${MYSQL_219_USER} ${MYSQL_219_PWD} ${MYSQL_219_IP} ${MYSQL_219_PORT} 


HISTORYDATE=`date -d "1 day ago" +%Y%m%d`
mytime=$HISTORYDATE
datestr=`date -d "$mytime"`
tst=`date -d "$mytime" +%s`
echo $tst
while [[ "`echo $datestr |awk '{print $4}'`" != "星期一" ]]
do
        let "tst=tst-86400"
        #echo $datestr
        datestr=`date -d "@$tst" +%Y%m%d`
done
datestr=`date -d "@$tst" +%Y%m%d`
echo "the day you wanted is:"$datestr

#middle file path 
midPath="/home/hadoop/shx/zhekou3qi/midddata/middata_"
pt_midPath="/usr/local/cloud/src/plugins/data/product_type.txt"
resPath="/home/hadoop/shx/zhekou3qi/midddata/resdata_"



#产品类型 中间结果
echo `date`"==========qyer_lastminute lastminute_main 写入middata==============="  
mysql -u${MYSQL_183_USER} -p${MYSQL_183_PWD} -h${MYSQL_183_IP} -P${MYSQL_183_PORT} -e " use qyer_lastminute; select id , product_type from qyer_lastminute.lastminute_main " > $pt_midPath



echo `date`"==========折扣三期的总pv和uv===============" 
/usr/local/cloud/hive/bin/hive -e  " 
use qyer;
add jar /home/hadoop/shx/zhekou3qi/job/ptbypid.jar;
CREATE TEMPORARY FUNCTION getProductType AS 'com.qyer.data.hive.udf.producttype.ProductTypeByPid';
select * from  (
	select  
		unix_timestamp() as addtime , 
		unix_timestamp ('$datestr' , 'yyyyMMdd') as fromtime, 
		IF( platform='mobile', 'm', platform ), 
		'A' as channel ,  
		type  ,
		'A' as ugc_type ,  
		'A' as product_type , 
		count(0) as pv ,
		count(distinct(unique_id)) as uv , 
		'1' as len
	from qyer.user_tracks
	where  logdate>='$datestr' and  logdate<='$HISTORYDATE' and type='1' and
		( platform ='mobile' or platform ='web')  
	group by  platform , type 

	union all 

	select 
	unix_timestamp() as addtime , 
	unix_timestamp ('$datestr' , 'yyyyMMdd') as fromtime ,
	IF( platform='mobile', 'm', platform ), 
	'A' as channel ,  
	type  ,
	ugc_type as ugc_type ,    
	'A' as product_type,
	count(0) as pv ,
	count(distinct(unique_id)) as uv, 
    '1' as len
	from qyer.user_tracks
	where   logdate>='$datestr' and  logdate<='$HISTORYDATE'   and type='1' and
	( platform ='mobile' or platform ='web')  and
	( ugc_type='mz_chose' or ugc_type='z_chose' or ugc_type ='mz_detail' or ugc_type='z_detail')
	group by platform , type ,  ugc_type 

	union all 

	select 
	unix_timestamp() as addtime , 
	unix_timestamp ('$datestr' , 'yyyyMMdd') as fromtime ,
	IF( platform='mobile', 'm', platform ), 
	'A' as channel ,  
	type  ,
	ugc_type as ugc_type ,    
	CASE regexp_extract(path , '(.*)/([a-z]+)/(.*)' , 2)
        WHEN 'flights' THEN '1016'
        WHEN 'hotels' THEN '1017'
        WHEN 'free' THEN '1018'
        WHEN 'freetour' THEN '9999'
        WHEN 'cruise' THEN '1020'
        WHEN 'car' THEN '1021'
        WHEN 'safe' THEN '1049'
        WHEN 'qz' THEN '1785'
        WHEN 'local' THEN '1983'
        WHEN 'ot' THEN '2161'
        WHEN 'other' THEN '1050'
        ELSE ''
        END  as product_type,
	count(0) as pv ,
	count(distinct(unique_id)) as uv , 
	'1' as len
	from qyer.user_tracks
	where   logdate>='$datestr' and  logdate<='$HISTORYDATE'   and type='1' and
	( platform ='mobile' or platform ='web')  and
	( ugc_type='mz_chose' or ugc_type='z_chose' )
	group by platform , type ,  ugc_type , regexp_extract(path , '(.*)/([a-z]+)/(.*)' , 2)


	union all 

	select 
	unix_timestamp() as addtime , 
	unix_timestamp ('$datestr', 'yyyyMMdd') as fromtime ,
	IF( platform='mobile', 'm', platform ), 
	'A' as channel ,  
	type  ,
	ugc_type as ugc_type ,    
	getProductType(ugc_content) as product_type,
	count(0) as pv ,
	count(distinct(unique_id)) as uv , 
	'1' as len
	from qyer.user_tracks
	where   logdate>='$datestr' and  logdate<='$HISTORYDATE'  and type='1' and
	( platform ='mobile' or platform ='web')  and
	( ugc_type='z_detail' or ugc_type='mz_detail')
	group by platform , type ,  ugc_type , getProductType(ugc_content)

	union all   
	  
	select 
		unix_timestamp() as addtime , 
		unix_timestamp ('$datestr' , 'yyyyMMdd') as fromtime , 
		IF( platform='mobile', 'm', platform ), 
		'A' as channel ,  
		type  ,
		'detail' as ugc_type ,   
		'A' as product_type , 
		count(0) as pv ,
		count(distinct(unique_id)) as uv , 
		'1' as len
	from qyer.user_tracks
	where  logdate>='$datestr' and  logdate<='$HISTORYDATE'  and type='2' and
	(category='m-lm-paydetail-booking' or category='lm-detail-booking-main' or category='lm-detail-booking-bar' or category='normal-booking') and
	( platform ='mobile' or platform ='web')  
	group by  platform , type 

	union all   

	select 
		unix_timestamp() as addtime , 
		unix_timestamp ('$datestr' , 'yyyyMMdd') as fromtime , 
		IF( platform='mobile', 'm', platform ), 
		'A' as channel ,  
		type  ,
		'detail' as ugc_type ,   
		getProductType(ugc_content) as product_type,
		count(0) as pv ,
		count(distinct(unique_id)) as uv , 
		'1' as len
	from qyer.user_tracks
	where   logdate>='$datestr' and  logdate<='$HISTORYDATE'   and type='2' and
	(category='m-lm-paydetail-booking' or category='lm-detail-booking-main' or category='lm-detail-booking-bar' or category='normal-booking') and
	( platform ='mobile' or platform ='web')  
	group by  platform , type ,getProductType(ugc_content)

	union all 

	select 
	unix_timestamp() as addtime , 
	unix_timestamp ('$datestr', 'yyyyMMdd') as fromtime ,
	'apph5' as platform ,  
	CASE regexp_extract(path , 'client_id=([a-zA-Z0-9_]+)(.*)' , 1)
        WHEN 'qyer_discount_ios' THEN 'H5_ios'
        WHEN 'qyer_discount_androi' THEN 'H5_Android'
        WHEN 'qyer_discount_hd' THEN 'H5_ios_HD'
        WHEN 'qyer_ios' THEN 'H5_qyer_ios'
        WHEN 'qyer_android' THEN 'H5_qyer_android'
        ELSE ''
    END  as channel, 
	type  ,
	regexp_extract(path , 'action=([a-zA-Z0-9_]+)(.*)' , 1) as ugc_type ,    
	getProductType(regexp_extract(path , 'id=([0-9]+)(.*)' , 1)) as product_type,
	count(0) as pv ,
	count(distinct(unique_id)) as uv , 
	'1' as len
	from qyer.user_tracks
	where   logdate>='$datestr' and  logdate<='$HISTORYDATE'    and type='1' and
	( platform ='apph5')  
	group by 
	type ,  
	regexp_extract(path , 'action=([a-zA-Z0-9_]+)(.*)' , 1) , 
	regexp_extract(path , 'client_id=([a-zA-Z0-9_]+)(.*)' , 1) , 
	getProductType(regexp_extract(path , 'id=([0-9]+)(.*)' , 1))


	union all 

	select 
	unix_timestamp() as addtime , 
	unix_timestamp ('$datestr' , 'yyyyMMdd') as fromtime ,
	'apph5' as platform ,  
	CASE regexp_extract(path , 'client_id=([a-zA-Z0-9_]+)(.*)' , 1)
        WHEN 'qyer_discount_ios' THEN 'H5_ios'
        WHEN 'qyer_discount_androi' THEN 'H5_Android'
        WHEN 'qyer_discount_hd' THEN 'H5_ios_HD'
        WHEN 'qyer_ios' THEN 'H5_qyer_ios'
        WHEN 'qyer_android' THEN 'H5_qyer_android'
        ELSE ''
    END  as channel, 
	type  ,
	'detail' as ugc_type ,    
	'A' as product_type,
	count(0) as pv ,
	count(distinct(unique_id)) as uv , 
	'1' as len
	from qyer.user_tracks
	where   logdate>='$datestr' and  logdate<='$HISTORYDATE'   and type='1' and
	( platform ='apph5')  
	group by 
	type ,  
	regexp_extract(path , 'client_id=([a-zA-Z0-9_]+)(.*)' , 1) 

	union all 

	select 
	unix_timestamp() as addtime , 
	unix_timestamp ('$datestr' , 'yyyyMMdd') as fromtime ,
	'apph5' as platform ,   
	CASE regexp_extract(path , 'client_id=([a-zA-Z0-9_]+)(.*)' , 1)
        WHEN 'qyer_discount_ios' THEN 'H5_ios'
        WHEN 'qyer_discount_androi' THEN 'H5_Android'
        WHEN 'qyer_discount_hd' THEN 'H5_ios_HD'
        WHEN 'qyer_ios' THEN 'H5_qyer_ios'
        WHEN 'qyer_android' THEN 'H5_qyer_android'
        ELSE ''
    END  as channel, 
	type  ,
	'detail' as ugc_type ,    
	getProductType(regexp_extract(path , 'id=([0-9]+)(.*)' , 1)) as product_type,
	count(0) as pv ,
	count(distinct(unique_id)) as uv , 
	'1' as len
	from qyer.user_tracks
	where   logdate>='$datestr' and  logdate<='$HISTORYDATE'    and type='1' and
	( platform ='apph5') 
	group by 
	type ,  
	regexp_extract(path , 'client_id=([a-zA-Z0-9_]+)(.*)' , 1) , 
	getProductType(regexp_extract(path , 'id=([0-9]+)(.*)' , 1))

	union all 

	select 
	unix_timestamp() as addtime , 
	unix_timestamp ('$datestr' , 'yyyyMMdd') as fromtime ,
	'apph5' as platform ,  
	CASE regexp_extract(path , 'client_id=([a-zA-Z0-9_]+)(.*)' , 1)
        WHEN 'qyer_discount_ios' THEN 'H5_ios'
        WHEN 'qyer_discount_androi' THEN 'H5_Android'
        WHEN 'qyer_discount_hd' THEN 'H5_ios_HD'
        WHEN 'qyer_ios' THEN 'H5_qyer_ios'
        WHEN 'qyer_android' THEN 'H5_qyer_android'
        ELSE ''
    END  as channel, 
	type  ,
	regexp_extract(path , 'action=([a-zA-Z0-9_]+)(.*)' , 1) as ugc_type ,    
	getProductType(regexp_extract(path , 'id=([0-9]+)(.*)' , 1)) as product_type,
	count(0) as pv ,
	count(distinct(unique_id)) as uv , 
	'1' as len
	from qyer.user_tracks
	where   logdate>='$datestr' and  logdate<='$HISTORYDATE'   and type='2' and
	( platform ='apph5')  and
	(category='m-lm-paydetail-booking') 
	group by 
	type ,  
	regexp_extract(path , 'action=([a-zA-Z0-9_]+)(.*)' , 1) , 
	regexp_extract(path , 'client_id=([a-zA-Z0-9_]+)(.*)' , 1) , 
	getProductType(regexp_extract(path , 'id=([0-9]+)(.*)' , 1))



	union all 

	select 
	unix_timestamp() as addtime , 
	unix_timestamp ('$datestr' , 'yyyyMMdd') as fromtime ,
	'apph5' as platform ,  
	CASE regexp_extract(path , 'client_id=([a-zA-Z0-9_]+)(.*)' , 1)
        WHEN 'qyer_discount_ios' THEN 'H5_ios'
        WHEN 'qyer_discount_androi' THEN 'H5_Android'
        WHEN 'qyer_discount_hd' THEN 'H5_ios_HD'
        WHEN 'qyer_ios' THEN 'H5_qyer_ios'
        WHEN 'qyer_android' THEN 'H5_qyer_android'
        ELSE ''
    END  as channel, 
	type  ,
	'detail' as ugc_type ,    
	'A' as product_type,
	count(0) as pv ,
	count(distinct(unique_id)) as uv , 
	'1' as len
	from qyer.user_tracks
	where   logdate>='$datestr' and  logdate<='$HISTORYDATE'   and type='2' and
	( platform ='apph5')  and
	(category='m-lm-paydetail-booking') 
	group by 
	type ,  
	regexp_extract(path , 'client_id=([a-zA-Z0-9_]+)(.*)' , 1) 

	union all 

	select 
	unix_timestamp() as addtime , 
	unix_timestamp ('$datestr', 'yyyyMMdd') as fromtime ,
	'apph5' as platform ,  
	CASE regexp_extract(path , 'client_id=([a-zA-Z0-9_]+)(.*)' , 1)
        WHEN 'qyer_discount_ios' THEN 'H5_ios'
        WHEN 'qyer_discount_androi' THEN 'H5_Android'
        WHEN 'qyer_discount_hd' THEN 'H5_ios_HD'
        WHEN 'qyer_ios' THEN 'H5_qyer_ios'
        WHEN 'qyer_android' THEN 'H5_qyer_android'
        ELSE ''
    END  as channel, 
	type  ,
	'detail' as ugc_type ,    
	getProductType(regexp_extract(path , 'id=([0-9]+)(.*)' , 1)) as product_type,
	count(0) as pv ,
	count(distinct(unique_id)) as uv , 
	'1' as len
	from qyer.user_tracks
	where   logdate>='$datestr' and  logdate<='$HISTORYDATE'   and type='2' and
	( platform ='apph5')  and
	(category='m-lm-paydetail-booking') 
	group by 
	type ,  
	regexp_extract(path , 'client_id=([a-zA-Z0-9_]+)(.*)' , 1) , 
	getProductType(regexp_extract(path , 'id=([0-9]+)(.*)' , 1))


) a " > $midPath$HISTORYDATE

if [ $? -eq 0 ]
  then
  	echo `date`"=============== middle file is ok ===============" $midPath$HISTORYDATE

  	#load to database  
	mysql -u${MYSQL_219_USER} -p${MYSQL_219_PWD} -h${MYSQL_219_IP} -P${MYSQL_219_PORT} -Dra -e " LOAD DATA LOCAL INFILE '$midPath$HISTORYDATE' INTO TABLE track_z_kpi_tmp  FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'"
  	if [ $? -eq 0 ]
  		then
  			echo `date`"================load data to database is ok !================" 
  		else
		  	echo `date`"================load data to database is error !================" 
		  	mailSend "147 :load data to database is error !"
	fi
else
  	echo `date`"================middle file is error !================" 
  	mailSend "147 :middle file is error !"
fi








